<!DOCTYPE html>
<html lang="zh-Hans">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 3.8.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"yoursite.com","root":"/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="A Quiet Departure.">
<meta property="og:type" content="website">
<meta property="og:title" content="Feng&#39;s Blog">
<meta property="og:url" content="http://yoursite.com/page/25/index.html">
<meta property="og:site_name" content="Feng&#39;s Blog">
<meta property="og:description" content="A Quiet Departure.">
<meta property="og:locale" content="zh-Hans">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Feng&#39;s Blog">
<meta name="twitter:description" content="A Quiet Departure.">

<link rel="canonical" href="http://yoursite.com/page/25/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : true,
    isPost : false,
    lang   : 'zh-Hans'
  };
</script>

  <title>Feng's Blog</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="Toggle navigation bar">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">Feng's Blog</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>Home</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>About</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>Tags</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>Categories</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>Archives</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content index posts-expand">
            
      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/04/07/oracle系列之DBLink的使用/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          
            <a href="/2019/04/07/oracle系列之DBLink的使用/" class="post-title-link" itemprop="url">oracle系列之DBLink的使用</a>
        </h2>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>

              <time title="Created: 2019-04-07 10:37:40" itemprop="dateCreated datePublished" datetime="2019-04-07T10:37:40+08:00">2019-04-07</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">Edited on</span>
                <time title="Modified: 2019-05-15 12:29:35" itemprop="dateModified" datetime="2019-05-15T12:29:35+08:00">2019-05-15</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/oracle/" itemprop="url" rel="index"><span itemprop="name">oracle</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
          <h2 id="赋予权限"><a href="#赋予权限" class="headerlink" title="赋予权限"></a>赋予权限</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- 查看feng用户是否具备创建database link 权限</span><br><span class="line">select * from user_sys_privs where privilege like upper(&apos;%DATABASE LINK%&apos;) AND USERNAME=&apos;FENG&apos;;</span><br><span class="line">-- 如果查出结果行说明没有权限，给FENG用户授予创建dblink的权限</span><br><span class="line">grant create public database link to feng;</span><br></pre></td></tr></table></figure>
<h2 id="使用DBLink"><a href="#使用DBLink" class="headerlink" title="使用DBLink"></a>使用DBLink</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">- sql创建DBlink，ORCL21是配置在tnsnames.ora的远程数据库的别名</span><br><span class="line">create public database link TESTLINK2 connect to FENG identified by &quot;123456&quot; USING &apos;ORCL21&apos;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>ORCL21=<br>   (DESCRIPTION =<br>    (ADDRESS_LIST =<br>      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))<br>    )<br>    (CONNECT_DATA =<br>      (SERVICE_NAME = reportdb)<br>    )<br>  )</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">--通过DBLink查询远程数据库的表</span><br><span class="line">select * from testTable@TESTLINK2 order by id;</span><br><span class="line">insert into testTable@TESTLINK2 values(...);</span><br><span class="line">udpate testTable@TESTLINK2 set infoColumn=&apos;test&apos; where ...;</span><br><span class="line">delete from testTable@TESTLINK2 where ...;</span><br></pre></td></tr></table></figure>
<h2 id="创建同义词"><a href="#创建同义词" class="headerlink" title="创建同义词"></a>创建同义词</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- 创建同义词</span><br><span class="line">create synonym TESTSYNONYM FOR testTable@TESTLINK2;</span><br><span class="line">-- 查询ORCL2中FENG用户的表TESTable</span><br><span class="line">SELECT * FROM TESTSYNONYM order by id</span><br></pre></td></tr></table></figure>

      
    </div>

    
    
    
      <footer class="post-footer">
        <div class="post-eof"></div>
      </footer>
  </article>
  
  
  

      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/04/28/Mac加虚拟机实现华为TSMAgent认证代理/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          
            <a href="/2019/04/28/Mac加虚拟机实现华为TSMAgent认证代理/" class="post-title-link" itemprop="url">Mac加虚拟机实现华为TSMAgent认证代理</a>
        </h2>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>
              

              <time title="Created: 2019-04-28 18:13:44 / Modified: 18:17:49" itemprop="dateCreated datePublished" datetime="2019-04-28T18:13:44+08:00">2019-04-28</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/Mac/" itemprop="url" rel="index"><span itemprop="name">Mac</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
          <p><a href="http://note.youdao.com/noteshare?id=38b5ac0d5bf70cd7796ca9a1e2f4bf4f" target="_blank" rel="noopener">http://note.youdao.com/noteshare?id=38b5ac0d5bf70cd7796ca9a1e2f4bf4f</a></p>

      
    </div>

    
    
    
      <footer class="post-footer">
        <div class="post-eof"></div>
      </footer>
  </article>
  
  
  

      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/04/07/数据库三范式/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          
            <a href="/2019/04/07/数据库三范式/" class="post-title-link" itemprop="url">数据库三范式</a>
        </h2>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>
              

              <time title="Created: 2019-04-07 10:34:44 / Modified: 10:36:23" itemprop="dateCreated datePublished" datetime="2019-04-07T10:34:44+08:00">2019-04-07</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/数据库/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
          <h2 id="第一范式"><a href="#第一范式" class="headerlink" title="第一范式"></a>第一范式</h2><p>当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">反例：</span><br><span class="line">表（联系人信息）</span><br><span class="line">正例:</span><br><span class="line">表（联系人姓名，手机号码，地址）</span><br></pre></td></tr></table></figure>
<p>1，每一列属性都是不可再分的属性值，确保每一列的原子性。<br>2，两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。</p>
<h2 id="第二范式"><a href="#第二范式" class="headerlink" title="第二范式"></a>第二范式</h2><p>如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">反例：</span><br><span class="line">订单表（订单号，价格，用户，用户名，身份证）</span><br><span class="line">正例：</span><br><span class="line">订单表和用户表分表。</span><br></pre></td></tr></table></figure>
<h2 id="第三范式"><a href="#第三范式" class="headerlink" title="第三范式"></a>第三范式</h2><p>设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.<br>数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–&gt;b–&gt;c  属性之间含有这样的关系，是不符合第三范式的。</p>
<p>比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）</p>
<p>这样一个表结构，就存在上述关系。 学号–&gt; 所在院校 –&gt; (院校地址，院校电话)</p>
<p>这样的表结构，我们应该拆开来，如下。</p>
<p>（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）</p>
<p><strong><em>三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求&gt;性能&gt;表结构。所以不能一味的去追求范式建立数据库。</em></strong></p>

      
    </div>

    
    
    
      <footer class="post-footer">
        <div class="post-eof"></div>
      </footer>
  </article>
  
  
  

      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/03/07/oracle系列之高级查询/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          
            <a href="/2019/03/07/oracle系列之高级查询/" class="post-title-link" itemprop="url">oracle系列之高级查询</a>
        </h2>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>

              <time title="Created: 2019-03-07 22:42:09" itemprop="dateCreated datePublished" datetime="2019-03-07T22:42:09+08:00">2019-03-07</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">Edited on</span>
                <time title="Modified: 2019-04-07 10:33:57" itemprop="dateModified" datetime="2019-04-07T10:33:57+08:00">2019-04-07</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/oracle/" itemprop="url" rel="index"><span itemprop="name">oracle</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
          <h2 id="分组查询"><a href="#分组查询" class="headerlink" title="分组查询"></a>分组查询</h2><h4 id="常用分组函数"><a href="#常用分组函数" class="headerlink" title="常用分组函数"></a>常用分组函数</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- avg,sum,min,max,count,wm_concat</span><br><span class="line">select avg(sal),sum(sal),min(sal),max(sal),count(sal) from emp;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select count(*),count(comm) from emp;</span><br></pre></td></tr></table></figure>
<p><strong><em>上面二个值不一样，是因为分组函数会自动忽略空值，会自动忽略空值。NVL函数可以使分组函数无法忽略空值，例如下面的sql：</em></strong><br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select count(*),count(nvl(comm, 0)) from emp;</span><br></pre></td></tr></table></figure></p>
<h4 id="group-by语句"><a href="#group-by语句" class="headerlink" title="group by语句"></a>group by语句</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select column, from table_name [where condition] [group by group_by_condition] [having group_condition] [order by column];</span><br><span class="line">-- 平均工资</span><br><span class="line">select deptno,avg(sal) from emp group by deptno;</span><br></pre></td></tr></table></figure>
<p><strong><em>错误写法：</em></strong><br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">select a,b,avg(x) from emp group by a;</span><br><span class="line">-- b必须出现在group by 后面。</span><br></pre></td></tr></table></figure></p>
<p><strong><em>注意：在select列表中所有未包含在组函数中的列都应该包含在group by子句中；包含在group by子句中的列不必包含在select列表中。</em></strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-- 按照部门，不同职位，统计员工的工资总额</span><br><span class="line">select deptno,job,sum(sal) from emp group by deptno,job order by deptno;</span><br><span class="line">-- 分组按照group by子句列的先后顺序。</span><br></pre></td></tr></table></figure>
<h4 id="过滤分组-having子句语法"><a href="#过滤分组-having子句语法" class="headerlink" title="过滤分组,having子句语法"></a>过滤分组,having子句语法</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- 求平均工资大于2000的部门</span><br><span class="line">select deptno,avg(sal) from emp group by deptno having avg(sal) &gt; 2000;</span><br></pre></td></tr></table></figure>
<p><strong><em>注意：where子句不能使用组函数，having才可以。没有组函数的时候，条件写在二者之后都是可以的，但是从sql优化的角度上，where的效率会更加高。</em></strong></p>
<blockquote>
<p>having是先分组，后过滤。where是先过滤，后分组。</p>
</blockquote>
<h4 id="分组函数的嵌套"><a href="#分组函数的嵌套" class="headerlink" title="分组函数的嵌套"></a>分组函数的嵌套</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- 求部门平均工资的最大值</span><br><span class="line">select max(avg(sal)) from emp group by deptno;</span><br></pre></td></tr></table></figure>
<h4 id="group-by-的增强-rollup-a-b"><a href="#group-by-的增强-rollup-a-b" class="headerlink" title="group by 的增强 rollup(a,b)"></a>group by 的增强 rollup(a,b)</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">group by rollup(a,b)</span><br><span class="line">-- 等价于</span><br><span class="line">group by a,b </span><br><span class="line">+</span><br><span class="line">group by a </span><br><span class="line">+</span><br><span class="line">group by null(不分组)</span><br></pre></td></tr></table></figure>
<p><strong><em>rollup用于报表的生成非常有用</em></strong></p>
<blockquote>
<p>break on deptno skip 2（相同的部门号只显示一次，不同的部门号跳过2行）<br>select deptno,job,sum(sal) from emp group by rollup(deptno, job);–生成升序报表</p>
</blockquote>
<h4 id="order-by-排序"><a href="#order-by-排序" class="headerlink" title="order by 排序"></a>order by 排序</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select deptno,avg(sal) 平均工资 from emp group by deptno order by 平均工资</span><br></pre></td></tr></table></figure>
<p><strong><em>上面平均工资也可以用2来代替，2代表select出来的列的序号。</em></strong></p>
<h2 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h2><h4 id="笛卡尔积"><a href="#笛卡尔积" class="headerlink" title="笛卡尔积"></a>笛卡尔积</h4><blockquote>
<p>假定有部门表dept和员工表emp</p>
</blockquote>
<p>笛卡尔全集的列数=dept行数*emp行数.<br>sql拼写的时候一定要注意避免错误的笛卡尔全集。</p>
<h4 id="等值连接"><a href="#等值连接" class="headerlink" title="等值连接"></a>等值连接</h4><blockquote>
<p>连接条件中如果是等于号，那就是等值连接。</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- 查询二个表的员工信息</span><br><span class="line">select ... from emp e, dept d where e.deptno=d.deptno</span><br></pre></td></tr></table></figure>
<h4 id="不等值连接"><a href="#不等值连接" class="headerlink" title="不等值连接"></a>不等值连接</h4><blockquote>
<p>连接条件中如果不是等于号，那就是等值连接。</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-- salgrade薪水级别表</span><br><span class="line">select ... from emp e, salgrade s where e.sal between s.lowsal and s.highsal</span><br><span class="line">-- 使用between一定要注意小的在前大的在后</span><br></pre></td></tr></table></figure>
<h4 id="外链接"><a href="#外链接" class="headerlink" title="外链接"></a>外链接</h4><blockquote>
<p>核心：通过外链接，把对于连接条件不成立的记录，仍然包含在最后的结果中。<br>左外连接：当连接条件不成立的时候，等号左边的表仍然被包含。<br>右外链接：当连接条件不成立的时候，等号右边的表仍然被包含。</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-- 右外链接</span><br><span class="line">select ... from emp e, dept d where e.deptno(+)=d.deptno group by ...;</span><br><span class="line">-- 左外链接反之</span><br></pre></td></tr></table></figure>
<h4 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h4><blockquote>
<p>通过别名，将同一张表视为多张表<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- 查询员工的老板，mgr员工老板的empno</span><br><span class="line">select ... from emp a, emp b where a.mgr=b.eepno;</span><br></pre></td></tr></table></figure></p>
</blockquote>
<h5 id="自连接存在的问题"><a href="#自连接存在的问题" class="headerlink" title="自连接存在的问题"></a>自连接存在的问题</h5><blockquote>
<p>根据笛卡尔积，n个别名表，自连接的总行数是表记录的n次方，所以自连接不适合操作大表，解决方法：层次查询。</p>
</blockquote>
<h4 id="层次查询"><a href="#层次查询" class="headerlink" title="层次查询"></a>层次查询</h4><blockquote>
<p>单表查询，树结构查询</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- 管理者和下属可以理解为树结构关系，emp.mgr指向管理者emp.empno</span><br><span class="line">select level, empno, ename, sal, mgr from emp connect by prior empno=mgr</span><br><span class="line">start with mgr is null order by 1;</span><br><span class="line">-- level是一个伪劣，相当于树结构的层次，越高层越小，从1开始，order by 1代表根据level排序</span><br></pre></td></tr></table></figure>
<blockquote>
<p>自连接优点是直观，缺点是不适合操作大表。<br>层次查询有点是单表查询，不会产生笛卡尔积，缺点是结果不直观，可能不满足某些应用场景。</p>
</blockquote>
<h2 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h2><blockquote>
<p>子查询说白了只是select语句的嵌套。</p>
<h4 id="子查询要注意的10个问题"><a href="#子查询要注意的10个问题" class="headerlink" title="子查询要注意的10个问题"></a>子查询要注意的10个问题</h4><ol>
<li>语法的小括号不可缺少</li>
<li>书写风格，让语句方便阅读</li>
<li>可以使用子查询的位置：where，select，having，from</li>
<li>不可以使用子查询的位置：group by</li>
<li>强调：from后面的子查询</li>
<li>主查询和子查询可以不是同一张表</li>
<li>一般不在子查询中使用排序，但在top-n分析问题中，必须对子查询排序</li>
<li>一般先执行子查询，再执行主查询；但是相关子查询例外</li>
<li>单行子查询只能使用单行操作符；多行子查询只能使用多行操作符</li>
<li>注意：子查询中是null值问题</li>
</ol>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">-- top-n分析问题</span><br><span class="line">-- 找到员工表中工资最高的前三名</span><br><span class="line">-- rownum：行号，伪列</span><br><span class="line"></span><br><span class="line">--以下是错误写法：</span><br><span class="line">select rownum, empno, ename, sal from emp where rownum&lt;=3 order by sal desc;</span><br></pre></td></tr></table></figure>
<p><strong><em>rownum 行号永远按照默认的顺序生成，行号只能使用&lt;, &lt;=; 不能使用&gt;, &gt;=。默认顺序是指select不加排序条件查出来的顺序，使用rownum排序是误区。</em></strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- 正确的写法</span><br><span class="line">select rownum, empno, ename, sal from（select * from emp order by sal desc）where rownum&lt;=3;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">--一般先执行子查询，再执行主查询；但是相关子查询例外,例如下面的相关子查询（子查询要依赖于父查询）。</span><br><span class="line">-- 找到员工表中薪水大于本部门平均薪水的员工</span><br><span class="line">select empno，ename，sal, (select avg(e2.sal) from emp e where e2.dept=e1.dept) avgsal from emp e where e.sal &gt; (select avg(e2.sal) from emp e where e2.dept=e1.dept);</span><br><span class="line">以上就是相关子查询实例，</span><br></pre></td></tr></table></figure>
<h4 id="子查询的类型"><a href="#子查询的类型" class="headerlink" title="子查询的类型"></a>子查询的类型</h4><blockquote>
<p>子查询分单行子查询和多行子查询。<br>多行操作符：in，any，all<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">-- 查询工资大于部门号30的任意一个员工工资的员工</span><br><span class="line">select * from emp e where e.sal &gt; any (select sal from emp where deptno=30);</span><br><span class="line">-- 或者使用min函数</span><br><span class="line">select * from emp e where e.sal &gt; (select min(sal) from emp where deptno=30);</span><br><span class="line">查询工资大于部门号30所有人工资的员工</span><br><span class="line">select * from emp e where e.sal &gt; any (select sal from emp where deptno=30);</span><br><span class="line">-- 同样可以用max函数</span><br><span class="line">-- 查询不是老板的员工</span><br><span class="line">-- 下面是错误写法：</span><br><span class="line">select * from emp where empno not in (select mgr from emp);--空结果</span><br><span class="line">--原因：a not in (10,20,null) == a!=10 and a!=20 and a!=null  最后一个条件永远不假</span><br><span class="line">-- 正确写法</span><br><span class="line">select * from emp where empno not in (select mgr from emp where mgr is not null);</span><br></pre></td></tr></table></figure></p>
</blockquote>
<h2 id="综合示例"><a href="#综合示例" class="headerlink" title="综合示例"></a>综合示例</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">-- 分页案例 查询排序后的5到8条记录</span><br><span class="line">select * from (select rownum r,* from (select * from emp order by desc) e1  where rownum &lt;= 8) e2 where e2.r &gt; 5;</span><br><span class="line"></span><br><span class="line">-- 按照入职年份查询员工的人数, 例如1981</span><br><span class="line">select count(*) total, sum(decode(to_char(hiredate, &apos;YYYY&apos;), &apos;1981&apos;, 1, 0)) &apos;1981&apos; from emp;</span><br><span class="line">select (select count(*) from emp) total, (select count(*) from emp where ...) &apos;1981&apos; fro dual;</span><br></pre></td></tr></table></figure>
<p><strong><em>相关子查询效率正常比多表查询快</em></strong></p>
<h2 id="Extra-notes"><a href="#Extra-notes" class="headerlink" title="Extra notes"></a>Extra notes</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">-- desc emp</span><br><span class="line">-- show user</span><br></pre></td></tr></table></figure>

      
    </div>

    
    
    
      <footer class="post-footer">
        <div class="post-eof"></div>
      </footer>
  </article>
  
  
  

      
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-Hans">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/03/20/一台机器启动多个jboss/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="Fengorz">
      <meta itemprop="description" content="A Quiet Departure.">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Feng's Blog">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          
            <a href="/2019/03/20/一台机器启动多个jboss/" class="post-title-link" itemprop="url">一台机器启动多个jboss</a>
        </h2>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">Posted on</span>
              

              <time title="Created: 2019-03-20 19:43:01 / Modified: 19:44:17" itemprop="dateCreated datePublished" datetime="2019-03-20T19:43:01+08:00">2019-03-20</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">In</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/jboss/" itemprop="url" rel="index"><span itemprop="name">jboss</span></a>
                </span>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
          <p><a href="https://jingyan.baidu.com/album/a17d52851442e78098c8f21b.html?picindex=4" target="_blank" rel="noopener">https://jingyan.baidu.com/album/a17d52851442e78098c8f21b.html?picindex=4</a></p>
<p>更改webserver的端口信息$JBoss_home\server\default\conf\jboss-service.xml文件<br>更改JNDI的端口信息$JBoss_home\server\default\conf\jboss-service.xml文件<br>更改RMI端口信息$JBoss_home\server\default\conf\jboss-service.xml文件<br>更改Remote端口信息$JBoss_home\server\default\conf\jboss-service.xml文件<br>更改JMS端口$JBoss_home\server\default\deploy\jms\uil2-service.xml文件<br>更改http端口$JBoss_home\server\default\deploy\jboss-web.deployer下的server.xml文件<br>更改ejb socket端口$JBoss_home\server\default\deploy\ejb3.deployer\META-INF\ jboss-service.xml</p>
<p>需要修改几个冲突的端口才能启动。</p>

      
    </div>

    
    
    
      <footer class="post-footer">
        <div class="post-eof"></div>
      </footer>
  </article>
  
  
  


  
  <nav class="pagination">
    <a class="extend prev" rel="prev" href="/page/24/"><i class="fa fa-angle-left" aria-label="Previous page"></i></a><a class="page-number" href="/">1</a><span class="space">&hellip;</span><a class="page-number" href="/page/24/">24</a><span class="page-number current">25</span>
  </nav>



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          Table of Contents
        </li>
        <li class="sidebar-nav-overview">
          Overview
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">Fengorz</p>
  <div class="site-description" itemprop="description">A Quiet Departure.</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">125</span>
          <span class="site-state-item-name">posts</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">68</span>
        <span class="site-state-item-name">categories</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">87</span>
        <span class="site-state-item-name">tags</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Fengorz</span>
</div>
  <div class="powered-by">Powered by <a href="https://hexo.io" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a>
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>
<script src="/js/utils.js"></script><script src="/js/motion.js"></script>
<script src="/js/schemes/pisces.js"></script>
<script src="/js/next-boot.js"></script>



  















  

  

</body>
</html>
